package com.bookrecomm.crawler.service;

import com.bookrecomm.crawler.entity.Book;
import com.bookrecomm.crawler.entity.Category;
import com.bookrecomm.crawler.entity.Tag;
import com.bookrecomm.crawler.entity.Comment;
import com.bookrecomm.crawler.util.DatabaseUtil;
import com.bookrecomm.crawler.util.HttpUtil;
import lombok.extern.slf4j.Slf4j;
import org.jsoup.Jsoup;
import org.jsoup.nodes.Document;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * 图书爬虫抽象基类
 * 实现通用的爬虫功能
 */
@Slf4j
public abstract class AbstractBookCrawler implements BookCrawler {
    
    /**
     * 获取网页文档对象
     * @param url 网页URL
     * @return Jsoup文档对象
     */
    protected Document getDocument(String url) {
        String html = HttpUtil.doGet(url);
        if (html != null && !html.isEmpty()) {
            try {
                return Jsoup.parse(html);
            } catch (Exception e) {
                log.error("解析HTML文档失败: {}", url, e);
            }
        }
        return null;
    }
    
    /**
     * 保存图书基本信息到数据库
     * @param book 图书实体
     * @return 保存后的图书ID，失败返回-1
     */
    protected long saveBookBasicInfo(Book book) {
        String sql = "INSERT INTO books (title, isbn, author, publisher, publish_date, language, price, " +
                "pages, cover_url, description, catalog, rating, rating_count, status, created_at, updated_at) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 1, NOW(), NOW())";
        
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        long bookId = -1;
        
        try {
            conn = DatabaseUtil.getConnection();
            conn.setAutoCommit(false);
            
            // 使用PreparedStatement，并获取生成的主键
            pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            pstmt.setString(1, book.getTitle());
            pstmt.setString(2, book.getIsbn());
            pstmt.setString(3, book.getAuthor());
            pstmt.setString(4, book.getPublisher());
            pstmt.setObject(5, book.getPublishDate());
            pstmt.setString(6, book.getLanguage());
            pstmt.setBigDecimal(7, book.getPrice());
            pstmt.setObject(8, book.getPages());
            pstmt.setString(9, book.getCoverUrl());
            pstmt.setString(10, book.getDescription());
            pstmt.setString(11, book.getCatalog());
            pstmt.setBigDecimal(12, book.getRating());
            pstmt.setInt(13, book.getRatingCount() != null ? book.getRatingCount() : 0);
            
            int rows = pstmt.executeUpdate();
            if (rows > 0) {
                // 获取生成的主键
                rs = pstmt.getGeneratedKeys();
                if (rs.next()) {
                    bookId = rs.getLong(1);
                    log.info("保存图书基本信息成功，ID: {}, 标题: {}", bookId, book.getTitle());
                }
            }
            
            conn.commit();
        } catch (SQLException e) {
            log.error("保存图书基本信息失败: {}", book.getTitle(), e);
            DatabaseUtil.rollback(conn);
        } finally {
            DatabaseUtil.close(conn, pstmt, rs);
        }
        
        return bookId;
    }
    
    /**
     * 保存图书分类关联信息
     * @param bookId 图书ID
     * @param categories 分类列表
     */
    protected void saveBookCategories(long bookId, List<Category> categories) {
        if (bookId <= 0 || categories == null || categories.isEmpty()) {
            return;
        }
        
        Connection conn = null;
        PreparedStatement pstmt = null;
        
        try {
            conn = DatabaseUtil.getConnection();
            conn.setAutoCommit(false);
            
            // 先查询或创建分类
            for (Category category : categories) {
                long categoryId = findOrCreateCategory(conn, category);
                if (categoryId > 0) {
                    // 创建图书与分类的关联
                    String sql = "INSERT INTO book_categories (book_id, category_id) VALUES (?, ?)";
                    pstmt = conn.prepareStatement(sql);
                    pstmt.setLong(1, bookId);
                    pstmt.setLong(2, categoryId);
                    pstmt.executeUpdate();
                    pstmt.close();
                }
            }
            
            conn.commit();
            log.info("保存图书分类关联成功，图书ID: {}", bookId);
        } catch (SQLException e) {
            log.error("保存图书分类关联失败，图书ID: {}", bookId, e);
            DatabaseUtil.rollback(conn);
        } finally {
            DatabaseUtil.close(conn, pstmt, null);
        }
    }
    
    /**
     * 查找或创建分类
     * @param conn 数据库连接
     * @param category 分类实体
     * @return 分类ID
     */
    private long findOrCreateCategory(Connection conn, Category category) throws SQLException {
        String querySql = "SELECT id FROM categories WHERE name = ?";
        PreparedStatement queryStmt = conn.prepareStatement(querySql);
        queryStmt.setString(1, category.getName());
        ResultSet rs = queryStmt.executeQuery();
        
        if (rs.next()) {
            long categoryId = rs.getLong("id");
            rs.close();
            queryStmt.close();
            return categoryId;
        } else {
            rs.close();
            queryStmt.close();
            
            // 创建新分类
            String insertSql = "INSERT INTO categories (name, description, level, status, created_at, updated_at) " +
                    "VALUES (?, ?, ?, 1, NOW(), NOW())";
            PreparedStatement insertStmt = conn.prepareStatement(insertSql, Statement.RETURN_GENERATED_KEYS);
            insertStmt.setString(1, category.getName());
            insertStmt.setString(2, category.getDescription());
            insertStmt.setInt(3, category.getLevel() != null ? category.getLevel() : 1);
            
            int rows = insertStmt.executeUpdate();
            if (rows > 0) {
                ResultSet generatedKeys = insertStmt.getGeneratedKeys();
                if (generatedKeys.next()) {
                    long categoryId = generatedKeys.getLong(1);
                    generatedKeys.close();
                    insertStmt.close();
                    return categoryId;
                }
                generatedKeys.close();
            }
            insertStmt.close();
        }
        
        return -1;
    }
    
    /**
     * 保存图书标签关联信息
     * @param bookId 图书ID
     * @param tags 标签列表
     */
    protected void saveBookTags(long bookId, List<Tag> tags) {
        if (bookId <= 0 || tags == null || tags.isEmpty()) {
            return;
        }
        
        Connection conn = null;
        PreparedStatement pstmt = null;
        
        try {
            conn = DatabaseUtil.getConnection();
            conn.setAutoCommit(false);
            
            // 先查询或创建标签
            for (Tag tag : tags) {
                long tagId = findOrCreateTag(conn, tag);
                if (tagId > 0) {
                    // 创建图书与标签的关联
                    String sql = "INSERT INTO book_tags (book_id, tag_id) VALUES (?, ?)";
                    pstmt = conn.prepareStatement(sql);
                    pstmt.setLong(1, bookId);
                    pstmt.setLong(2, tagId);
                    pstmt.executeUpdate();
                    pstmt.close();
                }
            }
            
            conn.commit();
            log.info("保存图书标签关联成功，图书ID: {}", bookId);
        } catch (SQLException e) {
            log.error("保存图书标签关联失败，图书ID: {}", bookId, e);
            DatabaseUtil.rollback(conn);
        } finally {
            DatabaseUtil.close(conn, pstmt, null);
        }
    }
    
    /**
     * 查找或创建标签
     * @param conn 数据库连接
     * @param tag 标签实体
     * @return 标签ID
     */
    private long findOrCreateTag(Connection conn, Tag tag) throws SQLException {
        String querySql = "SELECT id FROM tags WHERE name = ?";
        PreparedStatement queryStmt = conn.prepareStatement(querySql);
        queryStmt.setString(1, tag.getName());
        ResultSet rs = queryStmt.executeQuery();
        
        if (rs.next()) {
            long tagId = rs.getLong("id");
            rs.close();
            queryStmt.close();
            return tagId;
        } else {
            rs.close();
            queryStmt.close();
            
            // 创建新标签
            String insertSql = "INSERT INTO tags (name, description, heat, status, created_at, updated_at) " +
                    "VALUES (?, ?, ?, 1, NOW(), NOW())";
            PreparedStatement insertStmt = conn.prepareStatement(insertSql, Statement.RETURN_GENERATED_KEYS);
            insertStmt.setString(1, tag.getName());
            insertStmt.setString(2, tag.getDescription());
            insertStmt.setInt(3, tag.getHeat() != null ? tag.getHeat() : 0);
            
            int rows = insertStmt.executeUpdate();
            if (rows > 0) {
                ResultSet generatedKeys = insertStmt.getGeneratedKeys();
                if (generatedKeys.next()) {
                    long tagId = generatedKeys.getLong(1);
                    generatedKeys.close();
                    insertStmt.close();
                    return tagId;
                }
                generatedKeys.close();
            }
            insertStmt.close();
        }
        
        return -1;
    }
    
    /**
     * 保存图书评论信息
     * @param bookId 图书ID
     * @param comments 评论列表
     */
    protected void saveBookComments(long bookId, List<Comment> comments) {
        if (bookId <= 0 || comments == null || comments.isEmpty()) {
            return;
        }
        
        Connection conn = null;
        PreparedStatement pstmt = null;
        
        try {
            conn = DatabaseUtil.getConnection();
            conn.setAutoCommit(false);
            
            // 创建默认用户（爬虫用户）
            long userId = findOrCreateDefaultUser(conn);
            
            if (userId > 0) {
                String sql = "INSERT INTO comments (content, rating, likes, status, user_id, book_id, created_at, updated_at) " +
                        "VALUES (?, ?, ?, 1, ?, ?, NOW(), NOW())";
                
                for (Comment comment : comments) {
                    pstmt = conn.prepareStatement(sql);
                    pstmt.setString(1, comment.getContent());
                    pstmt.setBigDecimal(2, comment.getRating());
                    pstmt.setInt(3, comment.getLikes() != null ? comment.getLikes() : 0);
                    pstmt.setLong(4, userId);
                    pstmt.setLong(5, bookId);
                    pstmt.executeUpdate();
                    pstmt.close();
                }
                
                // 更新图书的评论数量
                String updateSql = "UPDATE books SET comment_count = comment_count + ? WHERE id = ?";
                pstmt = conn.prepareStatement(updateSql);
                pstmt.setInt(1, comments.size());
                pstmt.setLong(2, bookId);
                pstmt.executeUpdate();
            }
            
            conn.commit();
            log.info("保存图书评论成功，图书ID: {}，评论数量: {}", bookId, comments.size());
        } catch (SQLException e) {
            log.error("保存图书评论失败，图书ID: {}", bookId, e);
            DatabaseUtil.rollback(conn);
        } finally {
            DatabaseUtil.close(conn, pstmt, null);
        }
    }
    
    /**
     * 查找或创建默认用户（爬虫用户）
     * @param conn 数据库连接
     * @return 用户ID
     */
    private long findOrCreateDefaultUser(Connection conn) throws SQLException {
        String username = "crawler_user";
        String querySql = "SELECT id FROM users WHERE username = ?";
        PreparedStatement queryStmt = conn.prepareStatement(querySql);
        queryStmt.setString(1, username);
        ResultSet rs = queryStmt.executeQuery();
        
        if (rs.next()) {
            long userId = rs.getLong("id");
            rs.close();
            queryStmt.close();
            return userId;
        } else {
            rs.close();
            queryStmt.close();
            
            // 创建爬虫用户
            String insertSql = "INSERT INTO users (username, password, nickname, status, created_at, updated_at) " +
                    "VALUES (?, ?, ?, 1, NOW(), NOW())";
            PreparedStatement insertStmt = conn.prepareStatement(insertSql, Statement.RETURN_GENERATED_KEYS);
            insertStmt.setString(1, username);
            insertStmt.setString(2, "$2a$10$dXJ3SW6G7P50lGmMkkmwe.20cQQubK3.HZWzG3YB1tlRy.fqvM/BG"); // 加密的密码 "password"
            insertStmt.setString(3, "图书爬虫");
            
            int rows = insertStmt.executeUpdate();
            if (rows > 0) {
                ResultSet generatedKeys = insertStmt.getGeneratedKeys();
                if (generatedKeys.next()) {
                    long userId = generatedKeys.getLong(1);
                    generatedKeys.close();
                    insertStmt.close();
                    return userId;
                }
                generatedKeys.close();
            }
            insertStmt.close();
        }
        
        return -1;
    }
    
    @Override
    public boolean saveBook(Book book) {
        if (book == null) {
            return false;
        }
        
        try {
            // 保存图书基本信息
            long bookId = saveBookBasicInfo(book);
            if (bookId > 0) {
                // 保存图书分类关联
                if (book.getCategories() != null && !book.getCategories().isEmpty()) {
                    saveBookCategories(bookId, book.getCategories());
                }
                
                // 保存图书标签关联
                if (book.getTags() != null && !book.getTags().isEmpty()) {
                    saveBookTags(bookId, book.getTags());
                }
                
                // 保存图书评论
                if (book.getComments() != null && !book.getComments().isEmpty()) {
                    saveBookComments(bookId, book.getComments());
                }
                
                return true;
            }
        } catch (Exception e) {
            log.error("保存图书数据失败: {}", book.getTitle(), e);
        }
        
        return false;
    }
}
